package com.elitel.frame.business.service.impl;

import com.alibaba.fastjson.JSON;
import com.elitel.common.businessJdbc.BusinessInsertPreparedStatement;
import com.elitel.common.businessJdbc.BusinessInsertPreparedStatementHandler;
import com.elitel.common.businessJdbc.BusinessUpdatePreparedStatement;
import com.elitel.common.businessJdbc.BusinessUpdatePreparedStatementHandler;
import com.elitel.common.utils.XCommonUtils;
import com.elitel.frame.base.component.RedisCacheComponent;
import com.elitel.frame.base.component.RedisCacheDataBase;
import com.elitel.frame.business.dao.ext.SingleTableServerMapper;
import com.elitel.frame.business.service.SingleEditService;
import com.elitel.frame.business.service.TableFieldCommService;
import com.elitel.frame.main.entity.CfgDb;
import com.elitel.frame.main.entity.CfgEdit;
import com.elitel.frame.main.entity.CfgTable;
import com.elitel.frame.main.entity.ext.BaseEditconfigExt;
import com.elitel.frame.main.entity.vo.ValidationInfoVo;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.*;
import java.util.stream.Collectors;

/**
 * @Description 单表编辑服务接口实现
 *
 * @author guoyanfei
 * @date 2018/01/08
 */
@Service
public class SingleEditServiceImpl implements SingleEditService {
  private static final Logger logger = LoggerFactory.getLogger(SingleEditServiceImpl.class);

  @Autowired private TableFieldCommService tableFieldCommService;
  @Autowired private RedisCacheComponent redisCacheComponent;
  @Autowired private RedisCacheDataBase redisCacheDataBase;
  @Autowired private SingleTableServerMapper singleTableServerMapper;

  @Autowired
  private BusinessInsertPreparedStatementHandler businessInsertPreparedStatementHandler;

  @Autowired
  private BusinessUpdatePreparedStatementHandler businessUpdatePreparedStatementHandler;

  @SuppressWarnings("unchecked")
  @Override
  public ValidationInfoVo addInfo(CfgEdit cfgEdit, CfgDb cfgDb, String conQuery) {
    ValidationInfoVo validationInfoVo = new ValidationInfoVo();
    validationInfoVo.setCount(0);
    // 校验信息
    Map<String, Object> maps = tableFieldCommService.validateFieldValue(cfgDb, cfgEdit, conQuery, "add");
    if (XCommonUtils.isNotEmpty(maps)) {
      validationInfoVo.setIspass(Boolean.parseBoolean(maps.get("Ispass").toString()));
      validationInfoVo.setValidateMessage(maps.get("Message").toString());
    }
    if (validationInfoVo.getIspass()) {
      validationInfoVo = insertSingleData(cfgEdit, cfgDb, conQuery);
    }
    return validationInfoVo;
  }

  @SuppressWarnings("unchecked")
  @Override
  public ValidationInfoVo updateInfo(CfgEdit cfgEdit, CfgDb cfgDb, String conQuery) {
      ValidationInfoVo validationInfoVo = new ValidationInfoVo();
      validationInfoVo.setCount(0);
      // 校验信息
      Map<String, Object> maps = tableFieldCommService.validateFieldValue(cfgDb, cfgEdit, conQuery, "update");
      if (XCommonUtils.isNotEmpty(maps)) {
        validationInfoVo.setIspass(Boolean.parseBoolean(maps.get("Ispass").toString()));
        validationInfoVo.setValidateMessage(maps.get("Message").toString());
        if (!validationInfoVo.getIspass()) {
          return validationInfoVo;
        }
      }
      // 查询表名称
      CfgTable cfgTable = redisCacheComponent.cfgTableRedisCache(cfgEdit.getDtConfigId(), cfgEdit.getDbKey());
      if (cfgTable == null) {
        validationInfoVo.setIspass(false);
        validationInfoVo.setValidateMessage("修改的表不存在");
        return validationInfoVo;
      }

      // 拼接修改字段值
      List<BaseEditconfigExt> baseEditconfigExts = redisCacheDataBase.selectBaseEditconfigRedisCache(cfgEdit.getGuid());
      if(XCommonUtils.isEmpty(baseEditconfigExts)){
          validationInfoVo.setIspass(false);
          validationInfoVo.setValidateMessage("baseEditconfig 字段不存在(never happen)");
          return validationInfoVo;
      }

      Map<String, Object> updateColumnMap = new HashMap<String, Object>();
      Map<String, Object> conditionColumnMap = new HashMap<String, Object>();
      Map<String, String> fieldTypeMap = new HashMap<String, String>();
      Map<String, Object> updateParamMap = (Map<String, Object>) JSON.parse(conQuery);

      for (BaseEditconfigExt baseEditconfigExt : baseEditconfigExts) {
          String columnName = baseEditconfigExt.getDfname().trim();
          Object columnValue = updateParamMap.get(columnName);
          if(columnValue == null)
            continue;
          //
          // 判断是否为条件字段
          if (baseEditconfigExt.getIscondition().equals(1)) {
            conditionColumnMap.put(columnName, columnValue);
          } else {
            updateColumnMap.put(columnName, columnValue);
          }

          fieldTypeMap.put(columnName, baseEditconfigExt.getFieldType());
      }

      BusinessUpdatePreparedStatement bps = BusinessUpdatePreparedStatement.builder(cfgDb.getDbVersion())
                                                              .serviceGuid(cfgEdit.getGuid())
                                                              .tableName(cfgTable.getDtName())
                                                              .updateColumnMap(updateColumnMap)
                                                              .conditionColumnMap(conditionColumnMap)
                                                              .fieldTypeMap(fieldTypeMap)
                                                              .build();

      if(!bps.existsWhereCondition()){
          validationInfoVo.setIspass(false);
          validationInfoVo.setValidateMessage("where 条件不能为空! (未配置条件字段)");
          return validationInfoVo;
      }

      int count = businessUpdatePreparedStatementHandler.updateTableRowData(bps);
      validationInfoVo.setIspass(count> 0 ? true : false);
      validationInfoVo.setCount(count);

      return validationInfoVo;
  }

  // handle date
  public String handleDatetime(BaseEditconfigExt baseEditconfigExt, String databaseType){
      Map<String, String> mapvalidate = (Map<String, String>) JSON.parse(baseEditconfigExt.getValidatevalue());
      String validateCode = "datetime";

      if(mapvalidate == null || mapvalidate.get(validateCode) == null)
        return null;

      String validateValue = mapvalidate.get(validateCode);
      String validateJson = "";
      String fieldValue = null;

      if(XCommonUtils.isNotEmpty(validateValue)){
        validateJson = JSON.toJSONString(validateValue);
      }

      Map<String, String> mapvalidateDate = (Map<String, String>)JSON.parse(validateJson);
      String formatterDate = mapvalidateDate.get("format");
      switch(databaseType){
        default:
          fieldValue = "to_date ( " + fieldValue + " , '" + formatterDate + "' )";
      }

      return fieldValue;
  }

  @Override
  public ValidationInfoVo delInfo(CfgEdit cfgEdit, CfgDb cfgdb, String primaryKeys) {
    ValidationInfoVo validationInfoVo = new ValidationInfoVo();
    validationInfoVo.setCount(0);
    // 查询表名称
    CfgTable cfgTable =
        redisCacheComponent.cfgTableRedisCache(cfgEdit.getDtConfigId(), cfgEdit.getDbKey());
    if (cfgTable == null) {
      validationInfoVo.setIspass(false);
      validationInfoVo.setValidateMessage("删除的表不存在");
      return validationInfoVo;
    }
    String tableName = cfgTable.getDtName();
    String primaryKey = cfgTable.getDtPrimarykey();
    String[] pklist = primaryKey.split(",");
    String fieldValueList = "";
    for (Integer i = 0; i < pklist.length; i++) {
      if (fieldValueList.equals("")) {
        fieldValueList = pklist[i] + " in (" + getpkvaluestr(i, primaryKeys) + ")";
      } else {
        fieldValueList += " and " + pklist[i] + " in (" + getpkvaluestr(i, primaryKeys) + ")";
      }
    }

    String strsql = "delete from " + tableName + " where " + fieldValueList + " ";
    Integer count = 0;

    logger.warn("===========Begin单表编辑服务删除信息==============");
    logger.warn("服务ID：" + cfgEdit.getGuid());
    logger.warn("执行sql脚本：" + strsql);
    logger.warn("===========End单表编辑服务删除信息==============");

    // MSSql数据库特殊处理
    if (cfgdb.getDbVersion().equals("SQLSERVER")) {
      count = tableFieldCommService.executeMSSql(cfgdb, strsql);
    } else {
      count = singleTableServerMapper.executeAction(strsql);
    }

    if (count > 0) {
      validationInfoVo.setIspass(true);
    }
    validationInfoVo.setCount(count);
    return validationInfoVo;
  }

  /**
   * show 拆分多个主键值 by date 2018/02/24
   *
   * @param i 当前主键索引
   * @param pkvaluelist 主键值连接串
   * @return 返回当前主键值
   * @author guoyanfei
   */
  private String getpkvaluestr(Integer i, String pkvaluelist) {
    String result = "";
    String[] pklist = pkvaluelist.split(",");
    for (String pkvalue : pklist) {
      String[] strpk = pkvalue.split("_");
      if (result.equals("")) {
        result = "'" + strpk[i] + "'";
      } else {
        result += ",'" + strpk[i] + "'";
      }
    }
    return result;
  }

  @Override
  public List<Map<String, Object>> getAllData(CfgEdit cfgEdit, CfgDb cfgDb) {
    List<Map<String, Object>> maps = new ArrayList<Map<String, Object>>();
    // 查询表名称
    CfgTable cfgTable =
        redisCacheComponent.cfgTableRedisCache(cfgEdit.getDtConfigId(), cfgEdit.getDbKey());
    if (cfgTable != null) {
      String tableName = cfgTable.getDtName();
      String fields = "";
      List<BaseEditconfigExt> baseEditconfigExts =
          redisCacheDataBase.selectBaseEditconfigRedisCache(cfgEdit.getGuid());
      if (baseEditconfigExts != null && baseEditconfigExts.size() > 0) {
        for (BaseEditconfigExt baseEditconfigExt : baseEditconfigExts) {
          if (fields.equals("")) {
            fields = baseEditconfigExt.getDfname();
          } else {
            fields += "," + baseEditconfigExt.getDfname();
          }
        }
      }

      String sqltxt = "select " + fields + " from " + tableName + " ";
      maps = singleTableServerMapper.querySingleTBList(sqltxt);
    }
    return maps;
  }

  /**
   * show 单表批量导入数据
   *
   * @param cfgEdit
   * @param cfgDb
   * @param mapList 需导入的数据集合
   * @param flag 0错误就停止导入，1错误继续导入
   * @return ValidationInfoVo实体
   * @date 2018/01/23
   */
  @Override
  public ValidationInfoVo importData(
      CfgEdit cfgEdit, CfgDb cfgDb, List<Map<String, Object>> mapList, Integer flag) {
    ValidationInfoVo validationInfoVo = new ValidationInfoVo();
    validationInfoVo.setIspass(true);

    // 查询服务校验列
    List<BaseEditconfigExt> baseEditconfigExts =
        redisCacheDataBase.selectBaseEditconfigRedisCache(cfgEdit.getGuid());

    // 转换数据格式
    List<Map<String, Object>> dataList = new ArrayList<>();
    if (mapList != null && mapList.size() > 0) {
      if (baseEditconfigExts != null && baseEditconfigExts.size() > 0) {
        for (Map<String, Object> excelMap : mapList) {
          Map<String, Object> dataMap = new HashMap<>();
          for (Map.Entry<String, Object> entry : excelMap.entrySet()) {
            for (BaseEditconfigExt baseEditconfigExt : baseEditconfigExts) {
              if (baseEditconfigExt.getDfnameCn().trim().equalsIgnoreCase(entry.getKey().trim())) {
                dataMap.put(baseEditconfigExt.getDfname(), entry.getValue());
              }
            }
          }
          dataList.add(dataMap);
        }
      }
    }

    // 校验通过的数据
    List<Map<String, Object>> validationData = new ArrayList<Map<String, Object>>();
    // 验证数据
    Boolean IsValidate = true;
    if (dataList != null && dataList.size() > 0) {
      for (Map<String, Object> dataMap : dataList) {
        String dataJson = JSON.toJSONString(dataMap);
        // 校验信息
        Map<String, Object> maps =
            tableFieldCommService.validateFieldValue(cfgDb, cfgEdit, dataJson, "add");
        if (maps != null && maps.size() > 0) {
          validationInfoVo.setIspass(Boolean.parseBoolean(maps.get("Ispass").toString()));
          if (!Boolean.parseBoolean(maps.get("Ispass").toString())) {
            IsValidate = false;
            System.out.println(maps.get("Message").toString());
          } else {
            validationData.add(dataMap);
          }
        }
      }
    }

    // 批量插入数据
    if (flag.equals(1)) {
      // 反生错误继续导入
      validationInfoVo = batchImportData(validationData, cfgEdit, cfgDb);

    } else if (flag.equals(0)) {
      // 发生错误停止导入
      if (IsValidate) {
        validationInfoVo = batchImportData(validationData, cfgEdit, cfgDb);
      } else {
        validationInfoVo.setIspass(false);
        validationInfoVo.setCount(0);
        validationInfoVo.setValidateMessage("导入失败!");
      }
    }

    return validationInfoVo;
  }

  /**
   * show 批量导入已验证通过的数据
   *
   * @param validationData 需导入的数据
   * @param cfgEdit 服务信息
   * @param cfgDb 数据库连接信息
   * @return ValidationInfoVo实体
   * @author guoyanfei
   * @date 2018/01/23
   */
  public ValidationInfoVo batchImportData(List<Map<String, Object>> validationData, CfgEdit cfgEdit, CfgDb cfgDb) {
    ValidationInfoVo result = new ValidationInfoVo();
    Integer count = 0;
    for (Map<String, Object> valData : validationData) {
      String strData = JSON.toJSONString(valData);
      ValidationInfoVo singleData = insertSingleData(cfgEdit, cfgDb, strData);
      count += singleData.getCount();
    }
    result.setIspass(true);
    result.setCount(count);
    result.setValidateMessage("导入成功!");

    return result;
  }

  /**
   * show 单条数据插入
   *
   * @param cfgEdit 服务信息实体
   * @param cfgDb 数据源信息实体
   * @param jsonData 需插入的数据
   * @return 插入结果
   * @author guoyanfei
   * @date 2018/01/23
   */
  private ValidationInfoVo insertSingleData(CfgEdit cfgEdit, CfgDb cfgDb, String jsonData) {
    ValidationInfoVo validationInfoVo = new ValidationInfoVo();
    // 查询表名称
    CfgTable cfgTable = redisCacheComponent.cfgTableRedisCache(cfgEdit.getDtConfigId(), cfgEdit.getDbKey());
    if (cfgTable == null) {
      validationInfoVo.setIspass(false);
      validationInfoVo.setValidateMessage("新增的表不存在");
      return validationInfoVo;
    }

    List<BaseEditconfigExt> baseEditconfigExts = redisCacheDataBase.selectBaseEditconfigRedisCache(cfgEdit.getGuid());
    if(XCommonUtils.isEmpty(baseEditconfigExts)){
      validationInfoVo.setIspass(false);
      validationInfoVo.setValidateMessage("Can't find baseEditConfig");
      return validationInfoVo;
    }

    Map<String, String> columnNameMap = new HashMap<>();
    Map<String, Object> columnValueMap = new HashMap<>();
    Map<String, String> fieldTypeMap = new HashMap<>();
    // 新增值转换map
    Map<String, Object> passColumnMap = (Map<String, Object>) JSON.parse(jsonData);
    // here is convert baseEditConfigExt list to map key getDfname()
    Map<String, List<BaseEditconfigExt>> baseEditConfigExtByColumnName = baseEditconfigExts.stream().collect(Collectors.groupingBy(BaseEditconfigExt :: getDfname));

    for (Map.Entry<String, Object> passColumnObject : passColumnMap.entrySet()) {
      String columnName = passColumnObject.getKey().trim();
      if(XCommonUtils.isEmpty(columnName))
        continue;

      List<BaseEditconfigExt> baseEditConfigExtList = baseEditConfigExtByColumnName.get(columnName);
      if(XCommonUtils.isEmpty(baseEditConfigExtList))
        continue;

      BaseEditconfigExt baseEditconfigExt = baseEditConfigExtList.get(0);
      Map<String, Object> validateMap = (Map<String, Object>) JSON.parse(baseEditconfigExt.getValidatevalue());

      String validateCode = "";
      String validateJson = "";
      if (XCommonUtils.isNotEmpty(validateMap)) {
        for (Map.Entry<String, Object> validateEntry : validateMap.entrySet()) {// only existed one element entry
          validateCode = validateEntry.getKey();
          validateJson = validateEntry.getValue().toString();
        }
      }

      String columnValue = passColumnObject.getValue().toString().trim();
      if (validateCode.equals("guid"))
        columnValue = UUID.randomUUID().toString();

      columnNameMap.put(columnName, columnName);
      columnValueMap.put(columnName, columnValue);
      fieldTypeMap.put(columnName, baseEditconfigExt.getFieldType());
    }

    BusinessInsertPreparedStatement bps = BusinessInsertPreparedStatement.builder(cfgDb.getDbVersion())
                                            .serviceGuid(cfgEdit.getGuid())
                                            .tableName(cfgTable.getDtName())
                                            .columnNameMap(columnNameMap)
                                            .columnValueMap(columnValueMap)
                                            .fieldTypeMap(fieldTypeMap).build();

    int count = businessInsertPreparedStatementHandler.insertSingleRowData(bps);
    if (count > 0) {
      validationInfoVo.setIspass(true);
    }
    validationInfoVo.setCount(count);

    return validationInfoVo;
  }

  /**
   * show 单表编辑查询主键字段 实现方法
   *
   * @param cfgEdit 单表编辑服务实体
   * @param cfgDb 数据源实体
   * @return 主键串
   */
  @Override
  public String getPrimaryKey(CfgEdit cfgEdit, CfgDb cfgDb) {
    CfgTable cfgTable =
        redisCacheComponent.cfgTableRedisCache(cfgEdit.getDtConfigId(), cfgEdit.getDbKey());
    String primaryKeys = "";
    if (cfgTable != null) {
      primaryKeys = cfgTable.getDtPrimarykey();
    }
    return primaryKeys;
  }

  /**
   * 通过主键查询数据 create by guoyanfei on 2019/07/20 实现类
   *
   * @param cfgEdit 编辑服务实体
   * @param cfgDb 数据源
   * @param conQuery 查询条件
   * @return
   */
  @Override
  public List<Map<String, Object>> getDataPrimarykey(
      CfgEdit cfgEdit, CfgDb cfgDb, String conQuery) {
    List<Map<String, Object>> maps = new ArrayList<Map<String, Object>>();
    Map condition = null;
    if (null != conQuery && !conQuery.equals("")) {
      condition = (Map) JSON.parse(conQuery);
    }

    // 查询表名称
    CfgTable cfgTable =
        redisCacheComponent.cfgTableRedisCache(cfgEdit.getDtConfigId(), cfgEdit.getDbKey());
    if (condition != null && cfgTable != null) {
      String tablename = cfgTable.getDtName();
      String strwhere = " where 1=1 ";
      List<BaseEditconfigExt> baseEditconfigExts =
          redisCacheDataBase.selectBaseEditconfigRedisCache(cfgEdit.getGuid());
      if (baseEditconfigExts != null && baseEditconfigExts.size() > 0) {
        for (BaseEditconfigExt baseEditconfigExt : baseEditconfigExts) {
          if (baseEditconfigExt.getIscondition() == 1) {
            strwhere +=
                " and "
                    + baseEditconfigExt.getDfname()
                    + " = '"
                    + condition.get(baseEditconfigExt.getDfname())
                    + "'";
          }
        }
      }

      String sqltxt = "select * from " + tablename + " " + strwhere;

      logger.warn("===========Begin单表编辑服务通过主键查询信息==============");
      logger.warn("服务ID：" + cfgEdit.getGuid());
      logger.warn("执行sql脚本：" + sqltxt);
      logger.warn("===========End单表编辑服务通过主键查询信息==============");

      maps = singleTableServerMapper.querySingleTBList(sqltxt);
    }

    return maps;
  }
}
